package com.ezjie.ielts.db;

import java.io.File;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.ezjie.ielts.model.Eword;
import com.ezjie.ielts.util.DownloadUtil;
import com.ezjie.ielts.util.LogUtils;

/**
 * 单词的 dao操作
 * 
 * @author renyangyang
 *
 */
public class WordDao {
	private static final String TABLE_NAME = "e_word";
	private static final String COLUMN_WORD_ID = "word_id";
	private static final String COLUMN_WORD = "word";
	private static final String COLUMN_PHONETIC = "phonetic";
	private static final String COLUMN_EN_PHONETIC = "en_phonetic";
	private static final String COLUMN_LEVEL = "level";
	private static final String COLUMN_IS_ACTIVE = "is_active";
	private static final String COLUMN_CREATE_TIME = "create_time";
	private static final String COLUMN_UPDATE_TIME = "update_time";
	private SQLiteDatabase database;

	public WordDao(Context context) {
		DownloadUtil.initCacheDir(context);
		String databaseFilename = DownloadUtil.WORD_CACHE_PATH + "/" + DownloadUtil.WORD_DATABASE_FILENAME;
		File file = new File(databaseFilename);
		if (!file.exists()) {
			// 如果数据库文件不存在 就再拷贝一份
			DownloadUtil.getInstance().copyWordDB2SD(context);
		}

		if (file.exists()) {
			database = SQLiteDatabase.openOrCreateDatabase(databaseFilename, null);
		}
	}

	/**
	 * 根据单词id 查找单词
	 * 
	 * @param word_id
	 * @return
	 */
	public Eword findByWordId(int word_id) {
		Eword eword = new Eword();
		if (database == null) {
			return eword;
		}
		Cursor cursor = database.query(TABLE_NAME, null, COLUMN_WORD_ID + "=?", new String[] { word_id + "" }, null,
				null, null);
		while (cursor.moveToNext()) {
			eword.word_id = cursor.getInt(cursor.getColumnIndex(COLUMN_WORD_ID));
			eword.word = cursor.getString(cursor.getColumnIndex(COLUMN_WORD));
			eword.phonetic = cursor.getString(cursor.getColumnIndex(COLUMN_PHONETIC));
			eword.en_phonetic = cursor.getString(cursor.getColumnIndex(COLUMN_EN_PHONETIC));
			eword.level = cursor.getInt(cursor.getColumnIndex(COLUMN_LEVEL));
			eword.is_active = cursor.getInt(cursor.getColumnIndex(COLUMN_IS_ACTIVE));
			eword.create_time = cursor.getString(cursor.getColumnIndex(COLUMN_CREATE_TIME));
			eword.update_time = cursor.getString(cursor.getColumnIndex(COLUMN_UPDATE_TIME));

		}
		if (eword.word == null) {
			eword.word = "";
		}
		if (eword.phonetic == null) {
			eword.phonetic = "";
		}
		cursor.close();
		return eword;
	}

	/**
	 * 查询总数 is_active=1的
	 * 
	 * @return
	 */
	public int queryCountByIsActive() {
		int total = 0;
		if (database == null) {
			return total;
		}
		String sql = "select count(*) from " + TABLE_NAME + " where " + COLUMN_IS_ACTIVE + "=1";
		Cursor cursor = database.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			total = cursor.getInt(0);
		}
		return total;
	}

	public Eword findByWordIdOrNull(int word_id) {
		Eword eword = null;
		if (database == null) {
			return eword;
		}
		Cursor cursor = database.query(TABLE_NAME, null, COLUMN_WORD_ID + "=?", new String[] { word_id + "" }, null,
				null, null);
		while (cursor.moveToNext()) {
			eword = new Eword();
			eword.word_id = cursor.getInt(cursor.getColumnIndex(COLUMN_WORD_ID));
			eword.word = cursor.getString(cursor.getColumnIndex(COLUMN_WORD));
			eword.phonetic = cursor.getString(cursor.getColumnIndex(COLUMN_PHONETIC));
			eword.en_phonetic = cursor.getString(cursor.getColumnIndex(COLUMN_EN_PHONETIC));
			eword.level = cursor.getInt(cursor.getColumnIndex(COLUMN_LEVEL));
			eword.is_active = cursor.getInt(cursor.getColumnIndex(COLUMN_IS_ACTIVE));
			eword.create_time = cursor.getString(cursor.getColumnIndex(COLUMN_CREATE_TIME));
			eword.update_time = cursor.getString(cursor.getColumnIndex(COLUMN_UPDATE_TIME));
		}
		cursor.close();
		return eword;
	}

	public void insert(Eword eword) {
		if (database != null && eword != null) {
			ContentValues values = new ContentValues();
			values.put(COLUMN_WORD_ID, eword.word_id);
			values.put(COLUMN_WORD, eword.word);
			values.put(COLUMN_PHONETIC, eword.phonetic);
			values.put(COLUMN_EN_PHONETIC, eword.en_phonetic);
			values.put(COLUMN_LEVEL, eword.level);
			values.put(COLUMN_IS_ACTIVE, eword.is_active);
			values.put(COLUMN_CREATE_TIME, eword.create_time);
			values.put(COLUMN_UPDATE_TIME, eword.update_time);
			long insert = database.insert(TABLE_NAME, null, values);
			if (insert != -1) {
				LogUtils.d("插入word成功");
			} else {
				LogUtils.d("插入word失败");
			}
		}
	}

	public void update(Eword eword) {
		if (database != null && eword != null) {
			ContentValues values = new ContentValues();
			values.put(COLUMN_WORD_ID, eword.word_id);
			values.put(COLUMN_WORD, eword.word);
			values.put(COLUMN_PHONETIC, eword.phonetic);
			values.put(COLUMN_EN_PHONETIC, eword.en_phonetic);
			values.put(COLUMN_LEVEL, eword.level);
			values.put(COLUMN_IS_ACTIVE, eword.is_active);
			values.put(COLUMN_CREATE_TIME, eword.create_time);
			values.put(COLUMN_UPDATE_TIME, eword.update_time);
			int update = database
					.update(TABLE_NAME, values, COLUMN_WORD_ID + "=?", new String[] { eword.word_id + "" });
		}
	}

	public void updateList(List<Eword> list) {
		for (Eword eword : list) {
			Eword findByWordId = findByWordIdOrNull(eword.word_id);
			// 单词为空， 不存在
			if (findByWordId == null) {
				insert(eword);
			} else {
				update(eword);
			}
		}
	}

}
